﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TcpLabCommon;

namespace Common.GeneralClass
{
    public class ProjectDB
    {
        public int projectId;
        public string projectName;
        public DateTime startTime;
        public DateTime endTime;
        public int userId;
    }

    public class OperateProject
    {
        MyDb mydb = Mdl_Class.Mydb;
        MyDb mydb_other = Mdl_Class.Mydb_other;
        //添加项目信息
        public string AddProject(ProjectDB prj)
        {
            string i="";
            using (mydb)
            {
                try
                {

                    mydb.Open();
                    string sql=string.Format("select projectId from T_B_Project where projectName='{0}'",prj.projectName);
                    mydb.ExecuteReader(sql);
                    if (mydb.DataReader.Read())
                    {
                        i = mydb.DataReader["projectId"].ToString();
                    }
                    else
                    {
                        mydb.DataReader.Close();
                        sql = string.Format("insert into T_B_Project(projectName,startTime,endTime,userId) values('{0}',GetDate(),GetDate(),{1}) Select @@Identity", prj.projectName, prj.userId);
                        i = mydb.ExecuteScalar(sql);
                    }
                  
                    mydb.Close();
                }
                catch (Exception ex)
                {
                    i = "0";
                    Mdl_Class.WriteLog.dbErrLog("AddProject(ProjectDB prj)", ex.ToString());
                }
            }

            return i;
        }

        //删除项目信息
        public int DelProject(string proname)
        {
            int i = 0;
            using (mydb)
            {
                try
                {

                    mydb.Open();
                    string sql = string.Format("delete from  T_B_Project  where projectName='{0}'", proname);

                    i = mydb.ExecuteNonQuery(sql);
                    mydb.Close();
                }
                catch (Exception ex)
                {
                    Mdl_Class.WriteLog.dbErrLog("DelProject(string proname)", ex.ToString());
                }
            }

            return i;
        }


        //修改项目信息
        public int UpdateProject(int id)
        {
            int i = 0;
            using (mydb)
            {
                try
                {

                    mydb.Open();
                    string sql = string.Format("update T_B_Project set endTime=GetDate() where projectId={0}", id);

                    i = mydb.ExecuteNonQuery(sql);
                    mydb.Close();
                }
                catch (Exception ex)
                {
                    Mdl_Class.WriteLog.dbErrLog("UpdateProject(int id)", ex.ToString());
                }
            }

            return i;
        }

    

        //根据条件查询项目信息
        public List<ProjectDB> GetProjectBySql(string projectName, int projectId,DateTime date1,DateTime date2,bool state)
        {
            List<ProjectDB> prjlist = new List<ProjectDB>();

            using (mydb)
            {
                try
                {
                    mydb.Open();



                    string sql = "select * from T_B_Project where 1=1 ";
                    if (projectId != 0)
                    {
                        sql = sql + string.Format(" and projectId={0}", projectId);
                    }
                    if (projectName != "")
                    {
                        sql = sql + string.Format(" and projectName='{0}'", projectName);
                    }
                    if(state)
                    {
                        sql = sql + string.Format(" and startTime between '{0}' and '{1}'", date1, date2);
                    }
                    mydb.ExecuteReader(sql);
                    while (mydb.DataReader.Read())
                    {
                        ProjectDB prj = new ProjectDB();
                        prj.projectId = Convert.ToInt32(mydb.DataReader["projectId"]);
                        prj.projectName = mydb.DataReader["projectName"].ToString();
                        prj.startTime = Convert.ToDateTime(mydb.DataReader["startTime"].ToString());
                        prj.endTime = Convert.ToDateTime(mydb.DataReader["endTime"].ToString());
                        prj.userId = Convert.ToInt32(mydb.DataReader["userId"]);
                        prjlist.Add(prj);
                    }
                    mydb.Close();
                }
                catch (Exception ex)
                {
                    Mdl_Class.WriteLog.dbErrLog(" GetProjectBySql(string projectName, int projectId)", ex.ToString());
                }
            }
            return prjlist;
        }


      
    }
}
